const express = require('express');
const mysql = require('mysql');
// const favicon =require('server-favicon');
const server = express();
server.use(express.static('../../public'));
// server.use(favicon('./ceshi/favicon.ico'));
//请求以json的数据格式
server.use(express.json());
//请求头的数据类型为 x/www-form-urlencoded
server.use(express.urlencoded({extended:true}));
const db = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '439981',
    database : 'gxa281'
  });
//接收前端的数据
// server.get('/login',function(request,response){
//     //request  请求   前端的东西
//     //response 后台给前端的反馈
//     //request.query 前端发过来的get请求的数据
//     console.log(request.query);//
//     //做业务处理
//     //去数据库查询
//     const sql=`SELECT * FROM USER WHERE u_username='${request.query.username}' AND u_password='${request.query.password}';`;

//     db.query(sql,function(err,data){
//         console.log(err,data);
//         if(err){
//             console.log(err)
//         }else{
//             if(data.length){
//                 //有用户
//                 response.send({msg:'登录成功'})
//             }else{
//                 //没有用户
//                 response.send({msg:'账号密码错误'})
//             }
//         }
//     })
// })


// id:change_table.parents('tr').attr('data-id'),
// yuangong_id:a,
// yuangong_name:b,
// shuaka_time:c,
// beizhu:d,
// jcbs:s,
//添加原始刷卡数据
server.post('/add',(req,res)=>{
    const sql=`INSERT INTO yuanshishuaka VALUES(null,'${req.body.yuangong_name}',${req.body.yuangong_id},'${req.body.jcbs}','${req.body.shuaka_time}','${req.body.beizhu}')`;

    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'添加成功',data:data})
        }
    })
})



//获取原始刷卡数据
server.get('/get',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM yuanshishuaka LIMIT ${(page-1)*count},${count}`;
    const sql2=`select count(*) as total from yuanshishuaka;`;
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})
//删除原始刷卡数据
server.post('/del',(req,res)=>{
    const sql='delete from yuanshishuaka where shuaka_id="'+req.body.id+'";';
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'删除成功'})
        }
    })
})


//修改原始刷卡数据
server.post('/change',(req,res)=>{
    const sql=`update yuanshishuaka set jcbs='${req.body.jcbs}' , beizhu='${req.body.beizhu}' , shuaka_time='${req.body.shuaka_time}' , yuangong_id='${req.body.yuangong_id}' , yuangong_name='${req.body.yuangong_name}' where shuaka_id=${req.body.id};`;

    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'修改成功'})
        }
    })
})



// yuangong_id:a2,
// yuangong_name:b2,
// jcbs:s2
//搜索原始刷卡数据
server.get('/sousuo',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM yuanshishuaka where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}' and jcbs='${req.query.jcbs}' LIMIT ${(page-1)*count},${count};`;
    const sql2=`select count(*) as total from yuanshishuaka where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}' and jcbs='${req.query.jcbs}';`;
    db.query(sql,(err,data)=>{
        console.log(data)
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})

//req.query用来获取get方法传递的参数
//req.body用来获取post方法传递的参数


// leibie_num:a1,
// leibie:c1,
// leixing:b1,
// danwei:d1,
// beizhu:e1,
// chuangjianren:f1,
// cjTime:c1
//添加考勤类别
server.post('/add_xzkqlb',(req,res)=>{
    const sql=`INSERT INTO kaoqinleibie VALUES(null,'${req.body.leibie_num}','${req.body.leibie}','${req.body.leixing}','${req.body.danwei}','${req.body.beizhu}','${req.body.chuangjianren}','${req.body.cjTime}')`;

    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'添加成功',data:data})
        }
    })
})
//获取考勤类别
server.get('/get_xzkqlb',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM kaoqinleibie LIMIT ${(page-1)*count},${count}`;
    const sql2=`select count(*) as total from kaoqinleibie;`;
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})
//删除考勤类别
server.post('/del_xzkqlb',(req,res)=>{
    const sql='delete from kaoqinleibie where num ="'+req.body.id+'";';
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'删除成功'})
        }
    })
})
//修改考勤类别
server.post('/change_xzkqlb',(req,res)=>{
    const sql=`update kaoqinleibie set leibie_num='${req.body.leibie_num}' , leibie='${req.body.leibie}' , leixing='${req.body.leixing}' , danwei='${req.body.danwei}' , beizhu='${req.body.beizhu}' ,chuangjianren='${req.body.chuangjianren}' ,cjTime='${req.body.cjTime}' where num=${req.body.id};`;
    // console.log(${req.body.cjTime})
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'修改成功'})
        }
    })
})

// yuangong_id:a2,
// yuangong_name:b2,
// jcbs:s2
//搜索考勤类别
server.get('/sousuo_xzkqlb',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM kaoqinleibie where leibie_num='${req.query.leibie_num}' or leibie='${req.query.leibie}' or leixing='${req.query.leixing}' LIMIT ${(page-1)*count},${count};`;
    const sql2=`select count(*) as total from kaoqinleibie where leixing='${req.query.leixing}' or leibie='${req.query.leibie}'  or leibie_num='${req.query.leibie_num}';`;
    db.query(sql,(err,data)=>{
        console.log(data)
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})



// yuangong_id:a1,
// yuangong_name:b1,
// jblb:c1,
// jb_time:d1
//添加加班
server.post('/add_jiaban',(req,res)=>{
    const sql=`INSERT INTO jiaban VALUES('${req.body.yuangong_id}','${req.body.yuangong_name}','${req.body.jblb}',null,'${req.body.jb_time}')`;

    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'添加成功',data:data})
        }
    })
})
//获取加班
server.get('/get_jiaban',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM jiaban LIMIT ${(page-1)*count},${count}`;
    const sql2=`select count(*) as total from jiaban;`;
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})
//删除加班
server.post('/del_jiaban',(req,res)=>{
    const sql='delete from jiaban where jb_num ="'+req.body.id+'";';
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'删除成功'})
        }
    })
})
//修改加班
server.post('/change_jiaban',(req,res)=>{
    const sql=`update jiaban set yuangong_id='${req.body.yuangong_id}' , yuangong_name='${req.body.yuangong_name}' , jblb='${req.body.jblb}' , jb_time='${req.body.jb_time}' where jb_num=${req.body.id};`;
    // console.log(${req.body.cjTime})
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'修改成功'})
        }
    })
})



// yuangong_id:a2,
// yuangong_name:b2,
// jcbs:s2
//搜索加班
server.get('/sousuo_jiaban',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM jiaban where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}' LIMIT ${(page-1)*count},${count};`;
    const sql2=`select count(*) as total from jiaban where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}';`;
    db.query(sql,(err,data)=>{
        console.log(data)
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})


// yuangong_id:a1,
// yuangong_name:b1,
// qjlb:c1,
// qj_time:d1
//添加请假
server.post('/add_qingjia',(req,res)=>{
    const sql=`INSERT INTO qingjia VALUES('${req.body.yuangong_id}','${req.body.yuangong_name}','${req.body.qjlb}',null,'${req.body.qj_time}')`;

    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'添加成功',data:data})
        }
    })
})
//获取请假
server.get('/get_qingjia',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM qingjia LIMIT ${(page-1)*count},${count}`;
    const sql2=`select count(*) as total from qingjia;`;
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})
//删除请假
server.post('/del_qingjia',(req,res)=>{
    const sql='delete from qingjia where qj_num ="'+req.body.id+'";';
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'删除成功'})
        }
    })
})
//修改请假
server.post('/change_qingjia',(req,res)=>{
    const sql=`update qingjia set yuangong_id='${req.body.yuangong_id}' , yuangong_name='${req.body.yuangong_name}' , qjlb='${req.body.qjlb}' , qj_time='${req.body.qj_time}' where qj_num=${req.body.id};`;
    // console.log(${req.body.cjTime})
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'修改成功'})
        }
    })
})



// yuangong_id:a2,
// yuangong_name:b2,
// jcbs:s2
//搜索请假
server.get('/sousuo_qingjia',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM qingjia where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}' LIMIT ${(page-1)*count},${count};`;
    const sql2=`select count(*) as total from qingjia where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}';`;
    db.query(sql,(err,data)=>{
        console.log(data)
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})





//添加考勤分析与结账
server.post('/add_fx_jz',(req,res)=>{
    const sql=`INSERT INTO kaoqinfenxi VALUES(${req.body.yuangong_id},'${req.body.yuangong_name}',${req.body.qjcs},'${req.body.qjshichang}',${req.body.jbcs},'${req.body.jbshichang}','${req.body.fenxi}','${req.body.jiezhang}')`;

    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'添加成功',data:data})
        }
    })
})
//获取考勤分析与结账
server.get('/get_fx_jz',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM kaoqinfenxi LIMIT ${(page-1)*count},${count}`;
    const sql2=`select count(*) as total from kaoqinfenxi;`;
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})
//删除考勤分析与结账
server.post('/del_fx_jz',(req,res)=>{
    const sql='delete from kaoqinfenxi where yuangong_id ="'+req.body.id+'";';
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'删除成功'})
        }
    })
})
//修改考勤分析与结账
server.post('/change_fx_jz',(req,res)=>{
    const sql=`update kaoqinfenxi set yuangong_id=${req.body.yuangong_id} , yuangong_name='${req.body.yuangong_name}' , qjcs=${req.body.qjcs} , qjshichang='${req.body.qjshichang}' ,jbcs=${req.body.jbcs} ,jbshichang='${req.body.jbshichang}' ,fenxi='${req.body.fenxi}' ,jiezhang='${req.body.jiezhang}' where yuangong_id=${req.body.id};`;
    // console.log(${req.body.cjTime})
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'修改成功'})
        }
    })
})



// yuangong_id:a2,
// yuangong_name:b2,
// jcbs:s2
//搜索考勤分析与结账
server.get('/sousuo_fx_jz',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM kaoqinfenxi where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}' LIMIT ${(page-1)*count},${count};`;
    const sql2=`select count(*) as total from kaoqinfenxi where yuangong_id='${req.query.yuangong_id}' or yuangong_name='${req.query.yuangong_name}';`;
    db.query(sql,(err,data)=>{
        console.log(data)
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    console.log(data)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
                }
            })
        }
    })
})












server.listen(6537);